drop proc spa_SEC_CreateUser
go
/*********************************************************************************************            
* PROCEDURE: spa_SEC_CreateUser            
* PURPOSE: Create a new user from FE website            
* NOTES:            
* CREATED: Nha Doan 5/01/2011           
* MODIFIED:             
*/            
/*'-------------------------------------------------------------------------------------------------*            
' Date       | Version | Ticket #    |       Author             | Description            
' ========== | ======= | =========== | =========================| ====================================================================            
' 5/01/2011  |  1.00   | 00000       | Nha Doan                 | CREATED NEW.
'-------------------------------------------------------------------------------------------------*            
*/
/*
Ex:
[spa_SEC_CreateUser]            
	 @UserRecId = null,            
	 @LoginName = sysadmin,            
	 @ClientId = 1,            
	 @Email = 'admin@yahoo.com',
	 @UserNameFirst = 'Nha',            
	 @UserNameLast ='Doan',
	 @UserPassword ='6E-9B-3A-76-20-AA-F7-7F-36-27-75-15-09-77-EE-B8', --hashed password
	 @UserPasswordType = 2,-- 1: system password, 2: user's password
	 @Status = 2,-- 0:inactive, 1: wait for activation,  2: active, 3: locked out
	 @RoleIds = 1, --role id is seperated by ','            
	 @CreatedBy = null
*/            
create proc [spa_SEC_CreateUser]            
 @UserRecId uniqueidentifier = null output,            
 @LoginName varchar(30),            
 @ClientId int,            
 @Email varchar(250),
 @UserNameFirst varchar(100),            
 @UserNameLast varchar(100),
 @UserPassword varchar(100), --hashed password
 @UserPasswordType int = 1,-- 1: default password of import, 2: default password by system (reset password/create user user), 3: user's password
 @Status tinyint = 0,-- 0:inactive, 1: wait for activation,  2: active, 3: locked out
 @RoleIds varchar(100), --role id is seperated by ','            
 @CreatedBy uniqueidentifier = null
         
as            
declare 
	@error int , 
	@ThemeId int,
	@CurrentTime datetime
		
if @CreatedBy is not null 
BEGIN
	select @ThemeId = ThemeId
	from SEC_Users (nolock) where RecId = @CreatedBy    
END

set @CurrentTime = getdate()
            
            
set @error = 0            
begin tran            
            
set  @UserRecId = newid()            
--create new record in SEC_Users table            
            
insert into SEC_Users(            
 RecId,            
 ClientId,            
 LoginName,            
 UserNameFirst,            
 UserNameLast,               
 UserPassword,            
 UserPasswordType,            
 Email,            
 Status,          
 CreatedBy,
 ThemeId  
)            
values(            
 @UserRecId,            
 @ClientId,            
 @LoginName,            
 @UserNameFirst,            
 @UserNameLast,            
 @UserPassword,            
 @UserPasswordType,            
 @Email,            
 @Status,      
 @CreatedBy,
 @ThemeId    
)            
            
set @error = @error + @@error 

--analyse @RoleIds to get RoleId item and insert into SEC_UsersInRole table            
declare @RoleId int, @StrCounter int 
set @RoleIds = @RoleIds + ',' -- a small addjustment            
set @StrCounter = 0            
while @StrCounter <= len(@RoleIds)            
begin            
 set @RoleId = cast(substring(@RoleIds,@StrCounter,charindex(',',@RoleIds,@StrCounter) - @StrCounter) as int)            
 set @StrCounter = charindex(',',@RoleIds,@StrCounter) + 1     
            
 --do insert into SEC_UsersInRole table            
 insert into SEC_UsersInRole(            
  UserRecId,            
  RoleId
 )            
 values(            
  @UserRecId,            
  @RoleId
 )            
 set @error = @error + @@error            
end            
           
      
          
if (@error>0)            
 rollback tran -- operation has some errors, rollback all       
else            
 commit tran -- successfull
GO